﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlServerCe;

namespace Calc
{
    class CheckDb
    {
        string DbFilePath = @"C:\\Calc1232\";
        string DbName = "Calc123DB.sdf";
        string DbPath = "Data Source=" + @"C:\\Calc1232\Calc123DB.sdf";
        ManageItems objManageItems = new ManageItems();
        public void CheckDB()
        {
            //string s = "C:\\r.sdf";
            //FileInfo fn = new FileInfo(s);
            //if (fn.Exists == false)
            //{
            //    SqlCeEngine eng = new SqlCeEngine(s);
            //    eng.CreateDatabase();
            //}

            if (Directory.Exists(DbFilePath))
            {
                if (File.Exists(DbName))
                {
                    //return "yes";
                }
            }
            else
            {
                Directory.CreateDirectory(DbFilePath);
                SqlCeEngine Engine = new SqlCeEngine("Data Source=C:\\Calc1232\\Calc123DB.sdf");
                Engine.CreateDatabase();

                SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
                cn.Open();

                SqlCeCommand cmd = new SqlCeCommand("Create Table Groups (GroupID int primary key identity(1,1),GroupName nvarchar(100), CreatedDate Datetime,IsDeleted bit)", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into Groups (GroupName,CreatedDate,IsDeleted) values ('Group1','" + DateTime.Now + "',0)",cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into Groups (GroupName,CreatedDate,IsDeleted) values ('Group2','" + DateTime.Now + "',0)", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into Groups (GroupName,CreatedDate,IsDeleted) values ('Group3','" + DateTime.Now + "',0)", cn);
                cmd.ExecuteNonQuery();

                cmd=new SqlCeCommand("Create Table Items (ItemID int primary key identity(1,1),ItemName nvarchar(100),GroupID int,AssemblyID int,MaterialTypeID int,"+
                    "Cost money,CostPer nvarchar(100),Coverage nvarchar(100),Waste  nvarchar(100),Labour nvarchar(100),LabourType nvarchar(100),"+
                    "CreatedDate Datetime,IsDeleted bit)",cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into Items (ItemName,GroupID,MaterialTypeID,IsDeleted) values ('Item1',1,1,0)",cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into Items (ItemName,GroupID,MaterialTypeID,IsDeleted) values ('Item2',2,2,0)", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into Items (ItemName,GroupID,MaterialTypeID,IsDeleted) values ('Item3',3,2,0)", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Create Table Assemblies (AssemblyID int primary key identity(1,1),AssemblyName nvarchar(100), CreatedDate Datetime,IsDeleted bit)", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into Assemblies (AssemblyName) values ('SLAB')",cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into Assemblies (AssemblyName) values ('LAWN')", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into Assemblies (AssemblyName) values ('PAINT INTERIOR WALL')", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Create Table MaterialType (MaterialTypeID int primary key identity(1,1),MaterialName nvarchar(100),CreatedDate datetime,IsDeleted bit)",cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into MaterialType (MaterialName) values ('Material')", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into MaterialType (MaterialName) values ('Labour')", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into MaterialType (MaterialName) values ('Equipment')", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Create table ProjectDetails (ProjectID int primary key identity(1,1), ProjectName nvarchar(100),Address nvarchar(100),"+
                    "City nvarchar(100),State nvarchar(100),Zip nvarchar(100),Phone nvarchar(100),Fax nvarchar(100),Notes ntext,Status bit,"
                    +"DnyLbl1 nvarchar(100),DnyLbl2 nvarchar(100),DnyLbl3 nvarchar(100),DnyLbl4 nvarchar(100),DnyLbl5 nvarchar(100),"
                    + "txtDny1 nvarchar(100),txtDny2 nvarchar(100),txtDny3 nvarchar(100),txtDny4 nvarchar(100),txtDny5 nvarchar(100),CreatedDate datetime,IsDeleted bit)", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into ProjectDetails (ProjectName,Status,IsDeleted) values ('Project1',0,0)", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into ProjectDetails (ProjectName,Status,IsDeleted) values ('Project2',0,0)", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into ProjectDetails (ProjectName,Status,IsDeleted) values ('Project3',0,0)", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Create table UnitMaster (Id int primary key identity(1,1),MUnit nvarchar(100))", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into UnitMaster (MUnit) values ('Area')", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into UnitMaster (MUnit) values ('Length')", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into UnitMaster (MUnit) values ('Count')", cn);
                cmd.ExecuteNonQuery();

                cmd=new SqlCeCommand("Create table Measurements (MeasurementID int primary key identity(1,1),ProjectID int,Description nvarchar(2000),OriginalMeasurement float,CorrectedMeasurement float,Note nvarchar(2000),MeasurementUnits nvarchar(50),Layers nvarchar(100),HavingChild int,IsDeleted int)",cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Create Table MeasurementChild (ID int primary key identity(1,1),MeasurementID int,OriginalMeasurement float,CorrectedMeasurement float,Note nvarchar(2000),MeasurementUnits nvarchar(50),Layers nvarchar(100),ItemID int,ItemName nvarchar(100),GroupID int,MaterialTypeID int," +
                    "Cost money,CostPer nvarchar(100),Coverage nvarchar(100),Waste  nvarchar(100),Labour nvarchar(100),LabourType nvarchar(100)," +
                    "CreatedDate Datetime,IsDeleted int)", cn);
                cmd.ExecuteNonQuery();

                cmd=new SqlCeCommand("Create Table ConcreteCalculator (ConcreteID int primary key identity(1,1),MeasurementChildID int,SquareInches nvarchar(50),CubicYards nvarchar(50),"
                    +"Width nvarchar(50),Height1 nvarchar(50),Height2 nvarchar(50),Height3 nvarchar(50),Height4 nvarchar(50),Height5 nvarchar(50),"
                    +"Height6 nvarchar(50),Height7 nvarchar(50),Height8 nvarchar(50),Height9 nvarchar(50),Height10 nvarchar(50),Height11 nvarchar(50),"
                    +"Height12 nvarchar(50),CubicYards1 nvarchar(50),Diameter nvarchar(50),CubicYards2 nvarchar(50),Length1 nvarchar(50),"
                    +"Width1 nvarchar(50),Height nvarchar(50),CubicYards3 nvarchar(50),NumberofInches nvarchar(50),CubicYards4 nvarchar(50))",cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Create Table MiscItems(MiscItemID int primary key identity(1,1),MiscItemName nvarchar(50),MiscItemCost nvarchar(50),ProjectID int,IsDeleted int)", cn);
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand("Insert into MiscItems(MiscItemName,MiscItemCost,ProjectID,IsDeleted) values ('Misc1','43.34',3,0)", cn);
                cmd.ExecuteNonQuery();
                cn.Close();



            }
        }
    }
}
